-- EXEC SP_CHECK_IF_PROGRAM_CODE_EXISTS -1, '21;25', ' 1348;1349;1347', 'pc1'
/*
CREATED BY : MURALI KRISHNA YENGALA
PURPOSE    : CHECKS IF THE PROGRAM CODE ENTERED FOR THE FG ALREADY EXISTS FOR THE FGI_PART_NUMBER-CUSTOMER_NAME
             IF IT EXISTS, THE FGI_PART_NUMBER-CUSTOMER_NAME COMBINATION FOR THAT PROGRAM CODE IS RETURNED
*/

CREATE PROC SP_CHECK_IF_PROGRAM_CODE_EXISTS
@FG_PROCESS_STEP_ID INT,
@CUSTOMER_IDS VARCHAR(2000),
@FGI_PROCESS_STEP_IDS VARCHAR(2000),
@PROGRAM_CODE VARCHAR(50)
AS
BEGIN
	DECLARE @RESULTS TABLE( FGI_PART_NUMBER VARCHAR(50), CUSTOMER_NAME VARCHAR(65) )
	DECLARE @FGI_PART_NUMBER VARCHAR(50)
	DECLARE @CUSTOMER_NAME VARCHAR(65)
	DECLARE @QUERY_STRING NVARCHAR(1000)
	DECLARE @PARAMS NVARCHAR(1000)
	DECLARE @FGI_PROCESS_STEP_ID INT
	DECLARE @CUSTOMER_ID INT

	SET @CUSTOMER_IDS = REPLACE( @CUSTOMER_IDS, ';', ',' )
	SET @FGI_PROCESS_STEP_IDS = REPLACE( @FGI_PROCESS_STEP_IDS, ';', ',' )

	SET @QUERY_STRING = N'DECLARE FGI_ID_AND_CUSTOMER_ID_CURSOR CURSOR FOR SELECT FGI_PROCESS_STEP_ID, CUSTOMER_ID '
				+ 'FROM FG_TO_FGI_MAP_TABLE FGTFGI, FG_CUSTOMER_MAP_TABLE FGCM, PRODUCT P WHERE '
				+ 'FG_PROCESS_STEP_ID = FGCM.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = FGCM.PROCESSSTEP_ID '
				+ 'AND FGI_PROCESS_STEP_ID IN (' + @FGI_PROCESS_STEP_IDS + ') AND CUSTOMER_ID IN ( '
				+ @CUSTOMER_IDS + ') AND FGCM.PROCESSSTEP_ID <> @FG_PROCESS_STEP_ID AND '
				+ 'P_PROGRAMCODE = @PROGRAM_CODE'
	SET @PARAMS = N'@FG_PROCESS_STEP_ID INT, @PROGRAM_CODE VARCHAR(50)'
	EXECUTE SP_EXECUTESQL @QUERY_STRING, @PARAMS, @FG_PROCESS_STEP_ID, @PROGRAM_CODE

	OPEN FGI_ID_AND_CUSTOMER_ID_CURSOR
	FETCH NEXT FROM FGI_ID_AND_CUSTOMER_ID_CURSOR INTO @FGI_PROCESS_STEP_ID, @CUSTOMER_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @FGI_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @FGI_PROCESS_STEP_ID
		SELECT @CUSTOMER_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID
		INSERT @RESULTS VALUES( @FGI_PART_NUMBER, @CUSTOMER_NAME )
		FETCH NEXT FROM FGI_ID_AND_CUSTOMER_ID_CURSOR INTO @FGI_PROCESS_STEP_ID, @CUSTOMER_ID
	END
	CLOSE FGI_ID_AND_CUSTOMER_ID_CURSOR
	DEALLOCATE FGI_ID_AND_CUSTOMER_ID_CURSOR
	SELECT * FROM @RESULTS
END